#!/usr/bin/python3
# -*- coding: utf-8 -*-
import pymysql
import pymssql
from contextlib import closing
import traceback
import proto.config_pb2
from src.proto.config_pb2 import Account

def sql_handle_by_mediaData(account: Account, mediaData):
    try:
        db = pymysql.connect(host='127.0.0.1', user='Asgard', password='12345678', database='asgardtest', port=3306,
                                     charset='utf8')
        print("connect database successfully")

        #创建游标使用的cursor方法
        cursor = db.cursor()
        #使用execute方法执行sql语句
        cursor.execute('select version()')

        #使用fetchone方法获取单条数据
        data = cursor.fetchone()
        print('Database version:%s' % data)
        cursor.execute('''if exists (select 1 from  sys.objects where name='MEDIA' and  type='U')  drop table MEDIA''')
        # 创建表
        sql = """
                CREATE TABLE  MEDIA
                (ID INT PRIMARY KEY,
                NAME           TEXT,
                KIND           CHAR(50),
                TYPE           CHAR(50),
                DIR            TEXT,
                PATH           TEXT,
                SIZE           TEXT,
                CREATIME       TEXT,
                LASTCHANGETIME TEXT,
                LASTATTIME     TEXT);
        """
        cursor.execute(sql)
        print('Create table success')

        # 添加数据
        # 在一个conn.execute里面里面执行多个sql语句是非法的
        insert_sql = "INSERT INTO COMPANY (id,NAME,KIND,TYPE,DIR,PATH,SIZE,CREATIME,LASTCHANGETIME,LASTATTIME) VALUES ( %s, %s, %s, %s,%s, %s, %s, %s, %s)"
        tempData = ""
        for singleMedia in mediaData:
            temp += singleMedia.id
        cursor.executemany(insert_sql)
        

        # cursor.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )",
        #                 [('Paul', 32, 'California', 20000.00),
        #                     ('Allen', 25, 'Texas', 15000.00),
        #                     ('Teddy', 23, 'Norway', 20000.00),
        #                     ('Mark', 25, 'Rich-Mond', 65000.00),
        #                     ('David', 27, 'Texas', 85000.00),
        #                     ('Kim', 22, 'South-Hall', 45000.00),
        #                     ('James', 24, 'Houston', 10000.00)])
        #
        # 提交,否则重新运行程序时,表中无数据
        db.commit()
        print("insert successfully")
        # 查询表
        select_sql = """
            select id,NAME,KIND,TYPE,DIR,PATH,SIZE,CREATIME,LASTCHANGETIME,LASTATTIME FROM MEDIA
            """
        cursor.execute(select_sql)
        #
        for row in cursor.fetchall():
            print("-" * 50)  # 输出50个-,作为分界线
            print("%-10s %s" % ("id", row[0]))  # 字段名固定10位宽度,并且左对齐
            print("%-10s %s" % ("name", row[1]))
            print("%-10s %s" % ("kind", row[2]))
            print("%-10s %s" % ("type", row[3]))
            print("%-10s %s" % ("dir", row[4]))
            print("%-10s %s" % ("path", row[5]))  # 字段名固定10位宽度,并且左对齐
            print("%-10s %s" % ("size", row[6]))
            print("%-10s %s" % ("create_time", row[7]))
            print("%-10s %s" % ("last_changetime", row[8]))
            print("%-10s %s" % ("last_attime", row[9]))
        cursor.close()
        db.close()
    except pymssql.Error as e:
        print("mssql Error:", e)
        # traceback.print_exc()

def sql_handle_test():
    try:
        # 先要保证数据库中有test数据库
        # 获取一个数据库连接,with关键字 表示退出时,conn自动关闭
        # with 嵌套上一层的with 要使用closing()
        with closing(pymssql.connect(host='192.168.0.114', user='Asgard', password='12345678', database='asgardtest', port=1433,
                                     charset='utf8')) as conn:
            print("connect database successfully")
            # 获取游标,with关键字 表示退出时,cur自动关闭
            with conn.cursor() as cur:
                # 删除表
                cur.execute(
                        '''if exists (select 1 from  sys.objects where name='COMPANY' and  type='U')  drop table COMPANY''')
                # 创建表
                sql = """
                         CREATE TABLE  COMPANY
                       (ID INT  IDENTITY(1,1) PRIMARY KEY NOT NULL ,
                       NAME           TEXT    NOT NULL,
                       AGE            INT     NOT NULL,
                       ADDRESS        CHAR(50),
                       SALARY         REAL);
                """
                cur.execute(sql)
                print("create table successfully")

                # 添加数据
                # 在一个conn.execute里面里面执行多个sql语句是非法的
                cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )",
                                [('Paul', 32, 'California', 20000.00),
                                 ('Allen', 25, 'Texas', 15000.00),
                                 ('Teddy', 23, 'Norway', 20000.00),
                                 ('Mark', 25, 'Rich-Mond', 65000.00),
                                 ('David', 27, 'Texas', 85000.00),
                                 ('Kim', 22, 'South-Hall', 45000.00),
                                 ('James', 24, 'Houston', 10000.00)])
                # 提交,否则重新运行程序时,表中无数据
                conn.commit()
                print("insert successfully")
                # 查询表
                sql = """
                    select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
                 """
                cur.execute(sql)
                for row in cur.fetchall():
                    print("-" * 50)  # 输出50个-,作为分界线
                    print("%-10s %s" % ("id", row[0]))  # 字段名固定10位宽度,并且左对齐
                    print("%-10s %s" % ("name", row[1]))
                    print("%-10s %s" % ("age", row[2]))
                    print("%-10s %s" % ("address", row[3]))
                    print("%-10s %s" % ("salary", row[4]))
    except pymssql.Error as e:
        print("mssql Error:", e)
        # traceback.print_exc()